package com.shengruan.admin.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.shengruan.admin.vo.Orders;
import com.shengruan.admin.vo.Product;
import com.shengruan.common.Constants;
import com.shengruan.framework.jdbc.BaseJdbc;
import com.shengruan.framework.jdbc.BeanHandler;
import com.shengruan.framework.jdbc.BeanListHandler;
import com.shengruan.framework.jdbc.DBUtils;

public class OrdersJdbc  extends BaseJdbc {

    Logger logger = LoggerFactory.getLogger(getClass());

    public static final String ORDERS_INSERT = "INSERT INTO orders (userId, orderNumber, creationTime, freight, totalprice, description, STATUS, deliveryTime, shippingId, recipientName, streetAddress, zipCode, phoneNumber, mobileNumber, province, city, area, email, payBank)VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

    public static final String ORDERS_UPDATE = "UPDATE orders SET userId = ? , orderNumber = ? , creationTime = ? , freight = ? , totalprice = ? , description = ? , STATUS = ? , deliveryTime = ? , shippingId = ? , recipientName = ? , streetAddress = ? , zipCode = ? , phoneNumber = ? , mobileNumber = ? , province = ? , city = ? , AREA = ? , email = ?, payBank = ?, cancelTime = ? WHERE id = ?";

    public static final String ORDER_ITEMS_INSERT = "INSERT INTO order_items (orderId, productId, price, itemTotal)VALUES(?, ?, ?, ?)";

    public static final String ORDERS_LAST_INSERT_ID = "SELECT LAST_INSERT_ID() as LAST_INSERT_ID";


    public void save(Orders orders, List<Product> productList) {
        Connection con = this.getConnection();

        PreparedStatement psto = null;
        PreparedStatement pstoLastInsertId = null;
        ResultSet rsopstoLastInsertId = null;
        PreparedStatement pstoi = null;

        try {

          //  con.setAutoCommit(false);//设置事务为手动控制
            psto = con.prepareStatement(ORDERS_INSERT);
            psto.setInt(1, orders.getUserId());
            psto.setString(2, orders.getOrderNumber());
            psto.setLong(3, orders.getCreationTime());
            psto.setDouble(4, orders.getFreight());
            psto.setDouble(5, orders.getTotalprice());
            psto.setString(6, orders.getDescription());
            psto.setInt(7, orders.getStatus());
            psto.setLong(8, orders.getDeliveryTime());
            psto.setLong(9, orders.getShippingId());
            psto.setString(10, orders.getRecipientName());
            psto.setString(11, orders.getStreetAddress());
            psto.setInt(12, orders.getZipCode());
            psto.setString(13, orders.getPhoneNumber());
            psto.setString(14, orders.getMobileNumber());
            psto.setString(15, orders.getProvince());
            psto.setString(16, orders.getCity());
            psto.setString(17, orders.getArea());
            psto.setString(18, orders.getEmail());
            psto.setString(19, orders.getPayBank());

            psto.execute();

            pstoLastInsertId = con.prepareStatement(ORDERS_LAST_INSERT_ID);
            rsopstoLastInsertId = pstoLastInsertId.executeQuery();
            int lastInsertId = 0;
            if (rsopstoLastInsertId.next()) {
                lastInsertId = rsopstoLastInsertId.getInt("LAST_INSERT_ID");
            }


             //商品关联订单
            pstoi = con.prepareStatement(ORDER_ITEMS_INSERT);
            for (Product product : productList) {
                pstoi.setInt(1, lastInsertId);
                pstoi.setInt(2, product.getId());
                pstoi.setDouble(3, product.getBidRound());
                pstoi.setInt(4, 1);
                pstoi.execute();
            }

            //con.commit();//提交事务
           // con.setAutoCommit(true);
        } catch (SQLException e) {
            try {
                con.rollback();//回滚事务
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            DBUtils.close(pstoi);
            DBUtils.close(psto, con);
        }
    }

    public void save(Orders orders) {
        Connection conn = this.getConnection();
        Object[] pram = {orders.getUserId(), orders.getOrderNumber(), orders.getCreationTime(), orders.getFreight(), orders.getTotalprice(), orders.getDescription(), orders.getStatus(), orders.getDeliveryTime(), orders.getShippingId(), orders.getRecipientName(), orders.getZipCode(), orders.getPhoneNumber(), orders.getMobileNumber(), orders.getProvince(), orders.getCity(), orders.getArea()};
        try {
            this.execute(conn, ORDERS_INSERT, pram);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.close(conn);
        }
    }

    public void update(Orders orders) {
        Connection conn = this.getConnection();
        Object[] pram = {orders.getUserId(), orders.getOrderNumber(), orders.getCreationTime(), orders.getFreight(), orders.getTotalprice(), orders.getDescription(),
        orders.getStatus(), orders.getDeliveryTime(), orders.getShippingId(), orders.getRecipientName(), orders.getStreetAddress(), orders.getZipCode(), orders.getPhoneNumber(),
        orders.getMobileNumber(), orders.getProvince(), orders.getCity(), orders.getArea(), orders.getEmail(),orders.getPayBank(), orders.getCancelTime(), orders.getId()};
        try {
            this.execute(conn, ORDERS_UPDATE, pram);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.close(conn);
        }
    }


    public List<Orders> findAll(int userId) {
        List<Orders> ordersList = new ArrayList<Orders>();
        Connection conn = this.getConnection();
		String sql = "select * from orders o where o.userId = ? order by creationTime desc";
        
        //将商品图片查出来
        //String sql = "select * from orders o left join (SELECT oi.orderId,GROUP_CONCAT(p.smallPic) productSmallPic FROM order_items oi LEFT JOIN product p ON oi.productId = p.id GROUP BY oi.orderId) ooi ON o.id = ooi.orderId where o.userId = ? order by creationTime desc";
        Object[] pram = {userId};
		try {
			ordersList = this.query(conn, sql, new BeanListHandler<Orders>(Orders.class), pram);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.close(conn);
		}
		return ordersList;
    }

    public List<Orders> findAll(int userId, Integer status) {
        List<Orders> ordersList = new ArrayList<Orders>();
        Connection conn = this.getConnection();
		StringBuilder sql = new StringBuilder("select * from orders o where o.userId = ? ");

        if(Integer.valueOf(status) >=Constants.ORDERS_STATUS_WAITING_FOR_SHIPMENT)
            sql.append(" and o.status >= ?");
        else
            sql.append(" and o.status = ?");

        sql.append(" order by o.creationTime desc");

        //将商品图片查出来
        //String sql = "select * from orders o left join (SELECT oi.orderId,GROUP_CONCAT(p.smallPic) productSmallPic FROM order_items oi LEFT JOIN product p ON oi.productId = p.id GROUP BY oi.orderId) ooi ON o.id = ooi.orderId where o.userId = ? order by creationTime desc";
        Object[] pram = {userId, status};
		try {
			ordersList = this.query(conn, sql.toString(), new BeanListHandler<Orders>(Orders.class), pram);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.close(conn);
		}
		return ordersList;
    }


    public Orders getById(int userId, int id) {
        Connection conn = this.getConnection();
		String sql = "select * from orders o where o.userId = ? and o.id = ? order by creationTime desc";
        Object[] pram = {userId, id};
        Orders orders = null;
		try {
            orders = this.query(conn, sql.toString(), new BeanHandler<Orders>(Orders.class), pram);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.close(conn);
        }
        return orders;
    }

    public Orders getById(int id) {
        Connection conn = this.getConnection();
		String sql = "select * from orders o where o.id = ? order by creationTime desc";
        Object[] pram = {id};
        Orders orders = null;
		try {
            orders = this.query(conn, sql.toString(), new BeanHandler<Orders>(Orders.class), pram);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.close(conn);
        }
        return orders;
    }

    public Orders getByOrderNumber(String orderNumber) {
        Orders orders = null;
        Connection conn = this.getConnection();
        String sql = "select * from orders o where o.orderNumber = " + orderNumber;
        try {
            orders = this.query(conn, sql, new BeanHandler<Orders>(Orders.class));

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.close(conn);
        }
        return orders;
    }


    public int getRows(String orderNumber, String status, String ordersReport) {
        Connection conn = this.getConnection();
        StringBuffer sql = new StringBuffer("select count(o.id) from orders o where 1=1 ");

        if(StringUtils.isNotEmpty(ordersReport)) {
            if(Constants.ORDERS_REPORT_STATUS_PREVIOUS_WEEK == Integer.valueOf(ordersReport))
                sql.append(" and FROM_UNIXTIME(o.creationTime/1000)>=DATE_ADD(NOW(),INTERVAL -(8 + WEEKDAY(NOW())) DAY) AND FROM_UNIXTIME(o.creationTime/1000)<=DATE_ADD(NOW(),INTERVAL -(1 + WEEKDAY(NOW())) DAY)");
            else if(Constants.ORDERS_REPORT_STATUS_CURRENT_WEEK == Integer.valueOf(ordersReport))
                sql.append(" and WEEKOFYEAR(FROM_UNIXTIME(o.creationTime/1000))=WEEKOFYEAR(NOW())");
            else if(Constants.ORDERS_REPORT_STATUS_THREE_DAYS == Integer.valueOf(ordersReport))
                sql.append(" and TO_DAYS(NOW()) - TO_DAYS(FROM_UNIXTIME(o.creationTime/1000)) <= 3");
            else if(Constants.ORDERS_REPORT_STATUS_CURRENT_MONTH == Integer.valueOf(ordersReport))
                sql.append(" and MONTH(FROM_UNIXTIME(o.creationTime/1000))=MONTH(NOW()) AND YEAR(FROM_UNIXTIME(o.creationTime/1000))=YEAR(NOW())");
        }

        if(StringUtils.isNotEmpty(orderNumber)) sql.append(" and o.orderNumber = '" + StringUtils.defaultString(orderNumber) + "'");
        if(StringUtils.isNotEmpty(status))  sql.append(" and o.status = " + status);
        int count = 0;
        try {
            count = this.getRows(conn, sql.toString());
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.close(conn);
        }
        return count;
    }


    public List<Orders> findWithPage(int currentPage, int pageSize, String orderNumber, String status, String ordersReport) {
        List<Orders> ordersList = new ArrayList<Orders>();

        Connection conn = this.getConnection();
        StringBuffer sql = new StringBuffer("select o.*,u.userName,s.name shippingName from orders o left join users u on o.userId = u.id left join shipping s on o.shippingId = s.id where 1=1 ");

        if(StringUtils.isNotEmpty(ordersReport)) {
            if(Constants.ORDERS_REPORT_STATUS_PREVIOUS_WEEK == Integer.valueOf(ordersReport))
                sql.append(" and FROM_UNIXTIME(o.creationTime/1000)>=DATE_ADD(NOW(),INTERVAL -(8 + WEEKDAY(NOW())) DAY) AND FROM_UNIXTIME(o.creationTime/1000)<=DATE_ADD(NOW(),INTERVAL -(1 + WEEKDAY(NOW())) DAY)");
            else if(Constants.ORDERS_REPORT_STATUS_CURRENT_WEEK == Integer.valueOf(ordersReport))
                sql.append(" and WEEKOFYEAR(FROM_UNIXTIME(o.creationTime/1000))=WEEKOFYEAR(NOW())");
            else if(Constants.ORDERS_REPORT_STATUS_THREE_DAYS == Integer.valueOf(ordersReport))
                sql.append(" and TO_DAYS(NOW()) - TO_DAYS(FROM_UNIXTIME(o.creationTime/1000)) <= 3");
            else if(Constants.ORDERS_REPORT_STATUS_CURRENT_MONTH == Integer.valueOf(ordersReport))
                sql.append(" and MONTH(FROM_UNIXTIME(o.creationTime/1000))=MONTH(NOW()) AND YEAR(FROM_UNIXTIME(o.creationTime/1000))=YEAR(NOW())");
        }


        if(StringUtils.isNotEmpty(orderNumber)) sql.append(" and o.orderNumber = '" + StringUtils.defaultString(orderNumber) + "'");
        if(StringUtils.isNotEmpty(status))  sql.append(" and o.status = " + status);
        sql.append(" order by o.creationTime desc limit ?,?");
        try {
            int start = (currentPage - 1) * pageSize;
            ordersList = this.query(conn, sql.toString(), new BeanListHandler<Orders>(Orders.class), start, pageSize);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.close(conn);
        }
        return ordersList;
    }


    public void deleteById(int id) {
        Connection conn = this.getConnection();
        StringBuffer sql = new StringBuffer("delete from orders where id = ? ");
        try {
            this.execute(conn, sql.toString(), id);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.close(conn);
        }

        deleteOrderItems(id);
    }

    private void deleteOrderItems(int id) {
        Connection conn = this.getConnection();
        StringBuffer sql = new StringBuffer("delete from order_items where orderId = ? ");
        try {
            this.execute(conn, sql.toString(), id);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.close(conn);
        }
    }
}
